package com.hc.serviceImpl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.hc.bean.BSizeBean;
import com.hc.bean.GradeBean;
import com.hc.comm.CommData;
import com.hc.comm.Service.BSizeServie;
import com.hc.comm.Service.GetRoleService;
import com.hc.dao.RunSqlDao;
import com.hc.daoImpl.RunSqlDaoImpl;
import com.hc.service.CompanyGradeService;

public class CompanyGradeServiceImpl implements CompanyGradeService {
	
	static Logger log =  Logger.getLogger(CompanyGradeServiceImpl.class);

	@Override
	public GradeBean getAllCompanyGrade(String startDate, String stopDate) {

		
		
		
		int webTestCount = getWebTestCount(startDate,stopDate);;

		int webtimedelayGrade = getWebGradeCount(startDate,stopDate);
		int webShakeGrade = getWebShakeGrade(startDate,stopDate);
		int weblostRateGrade = getWeblostRateGrade(startDate,stopDate);
		
		
		
		int downFileTestCount = getDownFileTestCount(startDate,stopDate);;
	
		int downFileGradeCount = getDownFileGradeCount(startDate,stopDate);
		
		
		GradeBean bean = new GradeBean();
		bean.setId(0);
		
		if(downFileTestCount!=0){
			
			bean.setDownFileGrade(downFileGradeCount/downFileTestCount);
		}
	
		bean.setDownFileTestCount(downFileTestCount);
		bean.setTime(startDate+" 至  "+stopDate);
		
		if(webTestCount!=0){
			
			bean.setWebtimedelayGrade(webtimedelayGrade/webTestCount);
			bean.setWebShakeGrade(webShakeGrade/webTestCount);
			bean.setWeblostRateGrade(weblostRateGrade/webTestCount);
		}
		
		
		bean.setWebTestCount(webTestCount);
		bean.setVlanId("-");
		return bean;
	}

	
	private int getWeblostRateGrade(String startDate, String stopDate) {

		
		BSizeBean bean =  BSizeServie.getBSizeBean("丢包率");
		String sql1 = "select SUM(100) from webTestData where lostRate <="+bean.getMinSpeed()+"  and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		String sql2 = "select 	 CAST(SUM( 100*(1 - ( lostRate-"+bean.getMinSpeed()+")/900 ) )  as INT)   from webTestData where " +
				"lostRate >"+bean.getMinSpeed()+" and lostRate < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQueryNoKey(sql1);
	
		int coutnNumber1 = 0;
		if(list!=null&&list.size()>0){
			
			List obj =  (List) list.get(0);
			
			if(obj!=null&&obj.size()>0&&obj.get(0)!=null){
				
				coutnNumber1 = Integer.parseInt(obj.get(0).toString());
			}
			
		}
		
		List list2 = dao.runSqlQueryNoKey(sql2);
		
		int coutnNumber2 =0;
		if(list2!=null&&list2.size()>0){
			
			List obj2 =  (List) list2.get(0);
			
				if(obj2!=null&&obj2.size()>0&&obj2.get(0)!=null){
					
					coutnNumber2= Integer.parseInt( obj2.get(0).toString());
				}
			
		}

		
		
		return (coutnNumber1+coutnNumber2);
	
	}


	private int getWebShakeGrade(String startDate, String stopDate) {

		
		BSizeBean bean =  BSizeServie.getBSizeBean("网页时延抖动");
		
		
		
		
		
		String sql1 = "select SUM(100) from webTestData where shake <="+bean.getMinSpeed()+" and  lostRate!=100 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";

		
		String sql2 = "select 	 CAST(SUM( 100*(1 - ( shake-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT)   from webTestData where" +
				" shake >"+bean.getMinSpeed()+" and timedelayAvr < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQueryNoKey(sql1);
	
		int coutnNumber1 = 0;
		if(list!=null&&list.size()>0){
			
			List obj =  (List) list.get(0);
			
			if(obj!=null&&obj.size()>0&&obj.get(0)!=null){
				
				coutnNumber1 = Integer.parseInt(obj.get(0).toString());
			}
			
		}
		
		List list2 = dao.runSqlQueryNoKey(sql2);
		
		int coutnNumber2 =0;
		if(list2!=null&&list2.size()>0){
			
			List obj2 =  (List) list2.get(0);
			
				if(obj2!=null&&obj2.size()>0&&obj2.get(0)!=null){
					
					coutnNumber2= Integer.parseInt( obj2.get(0).toString());
				}
			
		}

		
		
		return (coutnNumber1+coutnNumber2);
	
	}


	public static void main(String[] args) {
		
		CompanyGradeService service = new CompanyGradeServiceImpl();
//		 GradeBean bean =  service.getAllCompanyGrade("2014-09-10 00:00:00","2014-09-12 00:00:00");
		 
	List<GradeBean> list = 	service.getCompanyGrade("2014-09-10 00:00:00","2014-09-12 00:00:00");
		 
			
			for(GradeBean bean:list){
				
				System.out.println(bean);
			}

		
	}
	
	private int getDownFileGradeCount(String startDate, String stopDate) {
		
		
		String downFileBSizeSql = getDownFileBSizeSql();	
		
		RunSqlDao dao = new RunSqlDaoImpl();
		List list = dao.runSqlQueryNoKey(downFileBSizeSql);
		
		
		int downFileGradeCount = 0;
		
		for(Object obj:list){
			
			List bsize =(List) obj;
			
			BSizeBean bean = BSizeServie.getBSizeBean(bsize.get(0).toString());
			
			
			if(bsize==null){
				continue;
			}
			
			String sql1 = "select SUM(100) from downFileTestData where speedMax >="+bean.getMaxSpeed()+" and bSizeNumber = '"+bsize.get(0).toString()+"' and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
			List result = dao.runSqlQueryNoKey(sql1);
			
			List obj1  = (List)result.get(0);
			if(obj1!=null&&obj1.size()>0&&obj1.get(0)!=null){
				
				downFileGradeCount+= Integer.parseInt( obj1.get(0).toString());
				
			}
	
			String sql2 = "select CAST(SUM( 100*(1 - ( speedMax-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT)  from downFileTestData where speedMax <="+bean.getMinSpeed()+" and bSizeNumber = '"+bsize.get(0).toString()+"' and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
			List result2 = dao.runSqlQueryNoKey(sql2);
			List obj2  = (List)result2.get(0);
			if(obj2!=null&&obj2.size()>0&& obj2.get(0)!=null){
				downFileGradeCount+= Integer.parseInt( obj2.get(0).toString());
			}
		}

		
		return downFileGradeCount;
	}

	private String getDownFileBSizeSql() {
		
		String sql ="select bSizeNumber from downFileTestData GROUP BY bSizeNumber";
		
		
		return sql;
	}


	private int getDownFileTestCount(String startDate, String stopDate) {
		String sql ="select COUNT(*) from downFileTestData where cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQueryNoKey(sql);
		int number=0;
		if(list!=null&&list.size()>0){
			
			List obj =  (List) list.get(0);
			
			if(obj!=null&&obj.size()>0&&obj.get(0)!=null){
				
				number = Integer.parseInt( obj.get(0).toString());
			}
			
		}
		
		return number;
	}

	private int getWebGradeCount(String startDate, String stopDate) {
		
		
		BSizeBean bean =  BSizeServie.getBSizeBean("网页时延");
		String sql1 = "select SUM(100) from webTestData where timedelayAvr <="+bean.getMinSpeed()+" and timedelayAvr>0 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		String sql2 = "select 	 CAST(SUM( 100*(1 - ( timedelayAvr-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT)   from webTestData where" +
				" timedelayAvr >"+bean.getMinSpeed()+" and timedelayAvr < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQueryNoKey(sql1);
	
		int coutnNumber1 = 0;
		if(list!=null&&list.size()>0){
			
			List obj =  (List) list.get(0);
			
			if(obj!=null&&obj.size()>0&&obj.get(0)!=null){
				
				coutnNumber1 = Integer.parseInt(obj.get(0).toString());
			}
			
		}
		
		List list2 = dao.runSqlQueryNoKey(sql2);
		
		int coutnNumber2 =0;
		if(list2!=null&&list2.size()>0){
			
			List obj2 =  (List) list2.get(0);
			
				if(obj2!=null&&obj2.size()>0&&obj2.get(0)!=null){
					
					coutnNumber2= Integer.parseInt( obj2.get(0).toString());
				}
			
		}

		
		
		return (coutnNumber1+coutnNumber2);
	}

	
	
	private int getWebTestCount(String startDate, String stopDate) {

		String sql ="select COUNT(*) from webTestData where cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'";
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQueryNoKey(sql);
		
		
		int number=0;
		if(list!=null&&list.size()>0){
			
			List obj =  (List) list.get(0);
			
			if(obj!=null&&obj.size()>0&&obj.get(0)!=null){
				
				number = Integer.parseInt( obj.get(0).toString());
			}
			
		}
		
		
		
		
		return number;
	}

	
	
	@Override
	public List<GradeBean> getCompanyGrade(String startDate, String stopDate) {

		
		
		
		Map<String, Integer> webTestCountList = getWebTestCountList(startDate,stopDate);;

		Map<String, Integer> webtimedelayGradeList = getWebtimedelayGradeList(startDate,stopDate);
		Map<String, Integer> webShakeGradeList = getWebShakeGradeListList(startDate,stopDate);	
		Map<String, Integer> weblostRateGradeList = getWeblostRateGradeListList(startDate,stopDate);
		
		
		Map<String, Integer> downFileTestCountList = getDownFileTestCountList(startDate,stopDate);;

		
		Set<String> vlanList= 	getVlanIdSet(webTestCountList,downFileTestCountList );
		
		
		
		Map<String, Integer> downFileGradeCountList = getDownFileGradeCountList(startDate,stopDate);
		
		
		List<GradeBean> list  = new ArrayList<GradeBean>();
		
		for(String key:vlanList){
			
			GradeBean bean = new GradeBean();
			
			
			bean.setId(0);
			bean.setDownFileGrade(0);
			
			
			if(downFileTestCountList.get(key)!=null){
				bean.setDownFileTestCount(downFileTestCountList.get(key));
				
			}
			
			bean.setTime(startDate+" 至  "+stopDate);
			
			if(webtimedelayGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWebtimedelayGrade(webtimedelayGradeList.get(key)/webTestCountList.get(key));

				
			}
			if(webShakeGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWebShakeGrade(webShakeGradeList.get(key)/webTestCountList.get(key));

				
			}
			if(weblostRateGradeList.get(key)!=null&&webTestCountList.get(key)!=null){
				bean.setWeblostRateGrade(weblostRateGradeList.get(key)/webTestCountList.get(key));
			}
			
			if(downFileGradeCountList.get(key)!=null&&downFileTestCountList.get(key)!=null){
				
				bean.setDownFileGrade(downFileGradeCountList.get(key)/downFileTestCountList.get(key));
				
			}
			
			if(webTestCountList.get(key)!=null){
				bean.setWebTestCount(webTestCountList.get(key));
				
			}
			bean.setVlanId(key);
			
			list.add(bean);
			
		}
		
		
		
		
		
		return list;
	}

	/**
	 * 下载文件得分
	 * @param startDate
	 * @param stopDate
	 * @return
	 */
	private Map<String, Integer> getDownFileGradeCountList(String startDate,
			String stopDate) {

		
		 Map<String, Integer> map = new HashMap<String, Integer>();
		
		String downFileBSizeSql = getDownFileBSizeSql();
		String vlanSql = getVlanSql();

		RunSqlDao dao = new RunSqlDaoImpl();
		List list = dao.runSqlQueryNoKey(downFileBSizeSql);

		List vlanlist = dao.runSqlQueryNoKey(vlanSql);

		for (Object vlanObj : vlanlist) {

			List vlan = (List) vlanObj;

			int downFileGradeCount = 0;

			for (Object obj : list) {

				List bsize = (List) obj;

				BSizeBean bean = BSizeServie.getBSizeBean(bsize.get(0)
						.toString());

				String sql1 = "select SUM(100) from downFileTestData where speedMax >"
						+ bean.getMaxSpeed()
						+ " and bSizeNumber = '"
						+ bsize.get(0).toString()
						+ "' and vlanId1='"+vlan.get(0).toString()+"' and cmdTime BETWEEN '"
						+ startDate + "' and '" + stopDate + "'";
				
				
				List result = dao.runSqlQueryNoKey(sql1);

				List obj1 = (List) result.get(0);
				
				if(obj1!=null&&obj1.size()>0&&obj1.get(0)!=null){
					
					downFileGradeCount += Integer.parseInt(obj1.get(0).toString());
				}
				
				

				String sql2 = "select CAST(SUM( 100*(( speedMax-"
						+ bean.getMinSpeed()
						+ ")/"
						+ (bean.getMaxSpeed() - bean.getMinSpeed())
						+ " ) )  as INT)  from downFileTestData where speedMax <="
						+ bean.getMaxSpeed() + " and speedMax>"+bean.getMinSpeed()+" and bSizeNumber = '"
						+ bsize.get(0).toString() + "' and vlanId1='"+vlan.get(0).toString()+"' and cmdTime BETWEEN '"
						+ startDate + "' and '" + stopDate + "'";
				List result2 = dao.runSqlQueryNoKey(sql2);

				List obj2 = (List) result2.get(0);
				
				if(obj2!=null&&obj2.size()>0&&obj2.get(0)!=null){
					
					downFileGradeCount += Integer.parseInt(obj2.get(0).toString());
				}
			}
			
			
			
			map.put(vlan.get(0).toString(), downFileGradeCount);
		}
		return map;

	}


	private String getVlanSql() {

		String  sql = "SELECT vlanId1 from downFileTestData GROUP BY vlanId1";
		
		return sql;
	}


	private Set<String> getVlanIdSet(Map<String, Integer> webTestCountList,
			Map<String, Integer> downFileTestCountList) {
		
		
		Set<String> set = new HashSet<String>();		
		


		for(String str:webTestCountList.keySet()){
			
			set.add(str);
			
		}
		
	for(String str:downFileTestCountList.keySet()){
			
			set.add(str);
			
		}

		

		
		
		
		return set;
	}

	private Map<String, Integer> getDownFileTestCountList(String startDate, String stopDate) {
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		String sql ="select COUNT(*) as count,vlanId1 from downFileTestData where cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
		RunSqlDao dao = new RunSqlDaoImpl();
		 List list =dao.runSqlQuery(sql);
		 if(list!=null&&list.size()>0){
				
				
				
				
				for(Object obj:list){
					
					
					Map map =(Map) obj;
					
					countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("count").toString()));
				}
					
				}
			
				return countMap;
	}

	private Map<String, Integer>  getWebtimedelayGradeList(String startDate, String stopDate) {
		BSizeBean bean =  BSizeServie.getBSizeBean("网页时延");
		
		String sql1 = "select SUM(100) as sumCount ,vlanId1 from webTestData where timedelayAvr <="+bean.getMinSpeed()+" and timedelayAvr>0 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";

		
		String sql2 = "select CAST(SUM( 100*(1 - ( timedelayAvr-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount  ,vlanId1 from webTestData where timedelayAvr >"+bean.getMinSpeed()+" and timedelayAvr < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQuery(sql1);
		
		
		List list2 = dao.runSqlQuery(sql2);
		
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		
		if(list!=null&&list.size()>0){
			
			for(Object obj:list){
				
				
				Map map =(Map) obj;
				
				countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
			}
		}
		if(list2!=null&&list2.size()>0){
			
			for(Object obj:list2){
				
				
				Map map =(Map) obj;
				
				if(countMap.get(map.get("vlanId1").toString())!=null){
					
					countMap.put(map.get("vlanId1").toString(), (Integer.parseInt(map.get("sumCount").toString())+countMap.get(map.get("vlanId1").toString())));
				}else{
					
					countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
				}
				
			}
		}
		
		return countMap ;
		
	}
	
	
private Map<String, Integer>  getWebShakeGradeListList(String startDate, String stopDate) {
		
	BSizeBean bean =  BSizeServie.getBSizeBean("网页时延抖动");
	
	
		String sql1 = "select SUM(100) as sumCount ,vlanId1 from webTestData where shake <="+bean.getMinSpeed()+" and lostRate!=100 and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
		String sql2 = "select CAST(SUM( 100*(1 - ( shake-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount  ,vlanId1 from webTestData where" +
				" shake >"+bean.getMinSpeed()+" and shake < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
		
		
		RunSqlDao dao = new RunSqlDaoImpl();
		
		
		List list = dao.runSqlQuery(sql1);
		
		
		List list2 = dao.runSqlQuery(sql2);
		
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		
		if(list!=null&&list.size()>0){
			
			for(Object obj:list){
				
				
				Map map =(Map) obj;
				
				countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
			}
		}
		if(list2!=null&&list2.size()>0){
			
			for(Object obj:list2){
				
				
				Map map =(Map) obj;
				
				if(countMap.get(map.get("vlanId1").toString())!=null){
					
					countMap.put(map.get("vlanId1").toString(), (Integer.parseInt(map.get("sumCount").toString())+countMap.get(map.get("vlanId1").toString())));
				}else{
					
					countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
				}
				
			}
		}
		
		return countMap ;
		
	}

private Map<String, Integer>  getWeblostRateGradeListList(String startDate, String stopDate) {
	
	BSizeBean bean =  BSizeServie.getBSizeBean("丢包率");
	
	
	String sql1 = "select SUM(100) as sumCount ,vlanId1 from webTestData where lostRate <="+bean.getMinSpeed()+"  and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";

	
	String sql2 = "select CAST(SUM( 100*(1 - ( lostRate-"+bean.getMinSpeed()+")/"+(bean.getMaxSpeed()-bean.getMinSpeed())+" ) )  as INT) as sumCount  ,vlanId1 from webTestData where " +
			"lostRate >"+bean.getMinSpeed()+" and lostRate < "+bean.getMaxSpeed()+" and cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
	
	
	RunSqlDao dao = new RunSqlDaoImpl();
	
	
	List list = dao.runSqlQuery(sql1);
	
	
	List list2 = dao.runSqlQuery(sql2);
	
	Map<String, Integer> countMap = new HashMap<String, Integer>();
	
	
	if(list!=null&&list.size()>0){
		
		for(Object obj:list){
			
			
			Map map =(Map) obj;
			
			countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
		}
	}
	if(list2!=null&&list2.size()>0){
		
		for(Object obj:list2){
			
			
			Map map =(Map) obj;
			
			if(countMap.get(map.get("vlanId1").toString())!=null){
				
				countMap.put(map.get("vlanId1").toString(), (Integer.parseInt(map.get("sumCount").toString())+countMap.get(map.get("vlanId1").toString())));
			}else{
				
				countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("sumCount").toString()));
			}
			
		}
	}
	
	return countMap ;
	
}
	
	

	private Map<String, Integer> getWebTestCountList(String startDate, String stopDate) {
		
		Map<String, Integer> countMap = new HashMap<String, Integer>();
		
		String sql ="select COUNT(*) as count,vlanId1 from webTestData where cmdTime BETWEEN '"+startDate+"' and '"+stopDate+"'  GROUP BY vlanId1";
		RunSqlDao dao = new RunSqlDaoImpl();
		List list =  dao.runSqlQuery(sql);
		
	
		if(list!=null&&list.size()>0){
			
			
			
			
			for(Object obj:list){
				Map map =(Map) obj;
				countMap.put(map.get("vlanId1").toString(), Integer.parseInt(map.get("count").toString()));
			}
			
		}
	
		return countMap;
		
	
	}


}
